#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
追号计划自动插入脚本

功能：
1. 查找所有未结束且 stop_on_win=true 的追号计划
2. 检查最后一期的中奖状态
3. 如果未中奖且未达到追号期数，插入下一期到模拟记录表
4. 如果中奖或已完成所有期数，标记追号计划为完成

执行时间：每日 00:05
Crontab: 5 0 * * * cd /path/to/lottery && python3 lottery-script/auto_insert_chase_periods.py

更新日志：
- v2.0: 添加并发安全保护（SELECT FOR UPDATE + INSERT IGNORE + 唯一索引）
"""

import os
import sys
import pymysql
import time
from datetime import datetime

# 数据库配置（优先使用环境变量，提高安全性）
DB_CONFIG = {
    'host': os.getenv('DB_HOST', '106.15.201.100'),
    'port': int(os.getenv('DB_PORT', 3306)),
    'user': os.getenv('DB_USER', 'root'),
    'password': os.getenv('DB_PASSWORD', 'qax1wsx23edc'),  # 建议设置环境变量 DB_PASSWORD
    'database': os.getenv('DB_NAME', 'lottery'),
    'charset': 'utf8mb4',
    'cursorclass': pymysql.cursors.DictCursor
}


class ChaseNumberAutoInserter:
    """追号计划自动插入处理器"""
    
    def __init__(self):
        self.conn = None
        self.cursor = None
        self.stats = {
            'checked': 0,
            'inserted': 0,
            'stopped': 0,
            'completed': 0,
            'errors': 0,
            'waiting': 0,  # 等待开奖
            'data_inconsistent': 0,  # 数据不一致
            'period_from_db': 0,  # 从数据库获取期号次数
            'period_smart_calc': 0,  # 智能计算期号次数
            'period_validation_failed': 0,  # 期号验证失败次数
            'concurrent_conflicts': 0  # 并发冲突次数（重复插入被阻止）
        }
    
    def connect_db(self, max_retries=3, retry_delay=2):
        """连接数据库（带重试机制）"""
        for attempt in range(1, max_retries + 1):
            try:
                self.conn = pymysql.connect(**DB_CONFIG)
                self.cursor = self.conn.cursor()
                print(f"[{datetime.now().strftime('%Y-%m-%d %H:%M:%S')}] ✅ 数据库连接成功")
                return True
            except Exception as e:
                print(f"[{datetime.now().strftime('%Y-%m-%d %H:%M:%S')}] ❌ 数据库连接失败 (尝试 {attempt}/{max_retries}): {e}")
                if attempt < max_retries:
                    print(f"  等待 {retry_delay} 秒后重试...")
                    time.sleep(retry_delay)
                else:
                    print(f"  已达到最大重试次数，连接失败")
                    return False
    
    def close_db(self):
        """关闭数据库连接"""
        if self.cursor:
            self.cursor.close()
        if self.conn:
            self.conn.close()
    
    def get_active_chase_plans(self):
        """获取所有需要处理的追号计划"""
        sql = """
            SELECT id, user_id, lottery_type, type, issue_number,
                   selected_numbers, bet_count, amount, multiple,
                   chase_count, stop_on_win, current_period_count,
                   last_inserted_period
            FROM user_chase_numbers
            WHERE status = 'active'
              AND current_period_count <= chase_count
            ORDER BY id
        """

        self.cursor.execute(sql)
        return self.cursor.fetchall()
    
    def get_last_period_status(self, chase_id, period_index):
        """获取最后一期的状态"""
        sql = """
            SELECT id, status, prize_amount, issue_number
            FROM user_simulations
            WHERE chase_id = %s AND period_index = %s
            LIMIT 1
        """

        self.cursor.execute(sql, (chase_id, period_index))
        return self.cursor.fetchone()
    
    def validate_issue_number(self, issue_number):
        """验证期号格式是否有效

        Args:
            issue_number: 期号字符串或整数

        Returns:
            (is_valid, error_message)
        """
        # 自动转换为字符串（容错处理）
        if isinstance(issue_number, int):
            issue_number = str(issue_number)

        if not issue_number:
            return False, "期号为空"

        if not isinstance(issue_number, str):
            return False, f"期号类型错误: {type(issue_number)}"

        # 期号应该只包含数字
        if not issue_number.isdigit():
            return False, f"期号包含非数字字符: {issue_number}"

        # 期号应该是5位或7位
        if len(issue_number) not in [5, 7]:
            return False, f"期号长度错误({len(issue_number)}位): {issue_number}"

        # 7位期号应该以20开头（2000-2099年）
        if len(issue_number) == 7:
            year_prefix = int(issue_number[:2])
            if year_prefix != 20:
                return False, f"7位期号应以20开头: {issue_number}"

        return True, ""

    def smart_increment_period(self, period_number, increment=1):
        """智能递增期号（处理跨天/跨月情况）

        Args:
            period_number: 期号（5位或7位字符串）
            increment: 递增数量

        Returns:
            递增后的期号（保持原格式）

        Note:
            这是兜底方案，优先使用从数据库获取的实际期号
        """
        # 确保 period_number 是字符串类型
        period_number = str(period_number)

        is_5_digit = len(period_number) == 5

        # 转换为7位格式进行计算
        if is_5_digit:
            year_prefix = "20"
            period_7digit = f"{year_prefix}{period_number}"
        else:
            period_7digit = period_number

        try:
            # 解析期号：2025293 -> 年份2025，期数293
            year = int(period_7digit[:4])
            period_num = int(period_7digit[4:])

            # 简单递增（大部分情况下够用）
            new_period = period_num + increment

            # 检查是否需要跨年（365期通常表示年末，366用于闰年）
            max_periods_per_year = 366  # 最大期数
            if new_period > max_periods_per_year:
                # 跨年处理：进入下一年，期数重新开始
                year += 1
                new_period = new_period - max_periods_per_year

            # 格式化新期号
            new_period_7digit = f"{year}{new_period:03d}"

            # 返回原格式
            if is_5_digit:
                return new_period_7digit[2:]  # 返回5位
            else:
                return new_period_7digit

        except Exception as e:
            # 异常情况下使用简单递增
            print(f"  ⚠️  期号智能递增失败: {e}，使用简单递增")
            return str(int(period_number) + increment)

    def check_if_period_drawn(self, lottery_type, issue_number):
        """检查该期是否已开奖"""
        # 确保 issue_number 是字符串类型
        issue_number = str(issue_number)

        # 支持5位和7位期号格式
        period_7digit = f"20{issue_number}" if len(issue_number) == 5 else issue_number

        sql = """
            SELECT period
            FROM lottery_results
            WHERE lottery_type = %s
              AND (period = %s OR period = %s)
            LIMIT 1
        """

        self.cursor.execute(sql, (lottery_type, issue_number, period_7digit))
        return self.cursor.fetchone() is not None

    def get_next_issue_number(self, lottery_type, current_issue_number):
        """从数据库获取下一期的实际期号"""
        # 确保 current_issue_number 是字符串类型
        current_issue_number = str(current_issue_number)

        # 不转换格式，直接用原格式查询（支持5位和7位）
        sql = """
            SELECT period
            FROM lottery_results
            WHERE lottery_type = %s
              AND CAST(period AS UNSIGNED) > CAST(%s AS UNSIGNED)
            ORDER BY CAST(period AS UNSIGNED) ASC
            LIMIT 1
        """

        self.cursor.execute(sql, (lottery_type, current_issue_number))
        result = self.cursor.fetchone()

        if result:
            # 返回与输入格式一致的期号
            next_period = str(result['period'])  # 确保是字符串类型

            # 保持格式一致：如果输入是5位，输出也是5位
            if len(current_issue_number) == 5 and len(next_period) == 7:
                next_period = next_period[2:]

            # ✅ 验证期号的合理性（不能倒退，不能跨度太大）
            current_num = int(current_issue_number[-5:]) if len(current_issue_number) >= 5 else int(current_issue_number)
            next_num = int(next_period[-5:]) if len(next_period) >= 5 else int(next_period)

            # 期号差距应该在合理范围内（1-150期）
            diff = next_num - current_num
            if diff <= 0 or diff > 150:
                print(f"  ⚠️  从数据库获取的期号{next_period}不合理（当前期{current_issue_number}），使用智能递增")
                next_period = self.smart_increment_period(current_issue_number, 1)
                print(f"  🔢 智能计算下一期: {current_issue_number} → {next_period}")
                self.stats['period_smart_calc'] += 1
                return next_period

            print(f"  📋 从数据库获取下一期期号: {current_issue_number} → {next_period}")
            self.stats['period_from_db'] += 1
            return next_period

        # 如果数据库中没有下一期，使用智能递增（兜底方案）
        print(f"  ⚠️  警告：数据库中未找到期号 {current_issue_number} 之后的期号，使用智能递增计算")
        next_period = self.smart_increment_period(current_issue_number, 1)
        print(f"  🔢 智能计算下一期: {current_issue_number} → {next_period}")
        self.stats['period_smart_calc'] += 1
        return next_period
    
    def insert_next_period(self, chase_plan):
        """插入下一期到模拟记录表（并发安全版本）"""
        try:
            # 【并发安全】Step 1: 锁定追号计划行，防止并发修改
            lock_sql = """
                SELECT current_period_count
                FROM user_chase_numbers
                WHERE id = %s
                FOR UPDATE
            """
            self.cursor.execute(lock_sql, (chase_plan['id'],))
            locked_row = self.cursor.fetchone()

            if not locked_row:
                print(f"  ❌ 追号计划不存在或已被删除")
                self.stats['errors'] += 1
                return False

            # 【并发安全】Step 2: 重新获取当前期数（可能被其他进程更新）
            current_count = locked_row['current_period_count']
            next_period_index = current_count + 1

            # 获取上一期的期号
            if next_period_index == 1:
                # 第一期，使用追号计划的起始期号
                next_issue_number = str(chase_plan['issue_number'])  # 确保是字符串类型
                print(f"  📝 第一期，使用起始期号: {next_issue_number}")
            else:
                # 从上一期获取期号，然后获取下一期的实际期号
                prev_period = self.get_last_period_status(chase_plan['id'], next_period_index - 1)
                if prev_period:
                    next_issue_number = self.get_next_issue_number(
                        chase_plan['lottery_type'],
                        prev_period['issue_number']
                    )
                else:
                    # 兜底：未找到上一期记录，使用智能递增计算
                    print(f"  ⚠️  警告：未找到第{next_period_index - 1}期记录，使用智能递增")
                    next_issue_number = self.smart_increment_period(
                        chase_plan['issue_number'],
                        next_period_index - 1
                    )
                    print(f"  🔢 智能计算第{next_period_index}期: {next_issue_number}")
                    self.stats['period_smart_calc'] += 1

            # 验证期号格式
            is_valid, error_msg = self.validate_issue_number(next_issue_number)
            if not is_valid:
                print(f"  ❌ 期号验证失败: {error_msg}")
                self.stats['errors'] += 1
                self.stats['period_validation_failed'] += 1
                return False

            # 【并发安全】Step 3: 使用INSERT IGNORE防止重复插入
            # 即使并发执行，数据库唯一索引也会阻止重复
            insert_sql = """
                INSERT IGNORE INTO user_simulations (
                    user_id, lottery_type, type, issue_number,
                    selected_numbers, bet_count, amount, multiple,
                    status, chase_id, period_index, created_at, updated_at
                ) VALUES (
                    %s, %s, %s, %s, %s, %s, %s, %s, 'pending', %s, %s, NOW(), NOW()
                )
            """

            self.cursor.execute(insert_sql, (
                chase_plan['user_id'],
                chase_plan['lottery_type'],
                chase_plan['type'],
                next_issue_number,  # 已经是字符串格式
                chase_plan['selected_numbers'],
                chase_plan['bet_count'],
                chase_plan['amount'],
                chase_plan['multiple'],
                chase_plan['id'],
                next_period_index
            ))

            # 【并发安全】Step 4: 检查是否真的插入了（INSERT IGNORE可能跳过）
            if self.cursor.rowcount == 0:
                print(f"  ⚠️  追号ID {chase_plan['id']} 第{next_period_index}期已存在（被其他进程插入），跳过")
                self.stats['concurrent_conflicts'] += 1
                return False

            # 更新追号计划
            update_sql = """
                UPDATE user_chase_numbers
                SET current_period_count = %s,
                    last_inserted_period = %s,
                    updated_at = NOW()
                WHERE id = %s
            """

            self.cursor.execute(update_sql, (
                next_period_index,
                next_issue_number,  # 已经是字符串格式
                chase_plan['id']
            ))

            self.conn.commit()

            print(f"  ✅ 追号ID {chase_plan['id']} 插入第{next_period_index}期（期号：{next_issue_number}）")
            self.stats['inserted'] += 1
            return True

        except pymysql.err.IntegrityError as e:
            # 【并发安全】处理唯一索引冲突
            if '1062' in str(e):  # Duplicate entry
                self.conn.rollback()
                print(f"  ⚠️  重复插入被数据库阻止（唯一索引生效）")
                self.stats['concurrent_conflicts'] += 1
                return False
            else:
                self.conn.rollback()
                print(f"  ❌ 数据库完整性错误: {e}")
                self.stats['errors'] += 1
                return False
        except Exception as e:
            self.conn.rollback()
            print(f"  ❌ 追号ID {chase_plan['id']} 插入失败: {e}")
            self.stats['errors'] += 1
            return False
    
    def mark_as_stopped(self, chase_id, reason):
        """标记追号计划为已停止"""
        try:
            sql = """
                UPDATE user_chase_numbers
                SET status = 'stopped',
                    updated_at = NOW()
                WHERE id = %s
            """
            
            self.cursor.execute(sql, (chase_id,))
            self.conn.commit()
            
            print(f"  🛑 追号ID {chase_id} 已停止（{reason}）")
            self.stats['stopped'] += 1
            return True
            
        except Exception as e:
            self.conn.rollback()
            print(f"  ❌ 追号ID {chase_id} 标记停止失败: {e}")
            self.stats['errors'] += 1
            return False
    
    def mark_as_completed(self, chase_id):
        """标记追号计划为已完成"""
        try:
            sql = """
                UPDATE user_chase_numbers
                SET status = 'completed',
                    updated_at = NOW()
                WHERE id = %s
            """

            self.cursor.execute(sql, (chase_id,))
            self.conn.commit()

            print(f"  ✔️  追号ID {chase_id} 已完成所有期数")
            self.stats['completed'] += 1
            return True

        except Exception as e:
            self.conn.rollback()
            print(f"  ❌ 追号ID {chase_id} 标记完成失败: {e}")
            self.stats['errors'] += 1
            return False

    def process_chase_plan(self, chase_plan):
        """处理单个追号计划"""
        chase_id = chase_plan['id']
        current_count = chase_plan['current_period_count']
        total_count = chase_plan['chase_count']
        stop_on_win = chase_plan['stop_on_win']

        print(f"\n处理追号ID {chase_id}（用户{chase_plan['user_id']}，彩种{chase_plan['lottery_type']}）")
        print(f"  当前期数: {current_count}/{total_count}，中奖即停: {'是' if stop_on_win else '否'}")

        # 0. 处理初始状态（未插入任何期数）
        if current_count == 0:
            print(f"  📝 初始状态，插入第一期")
            self.insert_next_period(chase_plan)
            return

        # 1. 获取最后一期的状态
        last_period = self.get_last_period_status(chase_id, current_count)

        if not last_period:
            print(f"  ⚠️  未找到第{current_count}期的记录，跳过")
            self.stats['errors'] += 1
            return

        print(f"  最后一期（{last_period['issue_number']}）状态: {last_period['status']}，奖金: {last_period['prize_amount']}")

        # 2. 检查最后一期是否已开奖
        is_drawn = self.check_if_period_drawn(
            chase_plan['lottery_type'],
            last_period['issue_number']
        )

        if not is_drawn:
            print(f"  ⏳ 最后一期（{last_period['issue_number']}）未开奖，等待开奖")
            self.stats['waiting'] += 1
            return

        # 3. 检查是否中奖（数据一致性检查）
        prize_amount = last_period['prize_amount'] or 0
        is_winning = last_period['status'] == 'winning'

        # 数据一致性警告
        if is_winning and prize_amount <= 0:
            print(f"  ⚠️  警告：状态为winning但奖金为{prize_amount}，数据不一致")
            self.stats['data_inconsistent'] += 1
        elif not is_winning and prize_amount > 0:
            print(f"  ⚠️  警告：状态为{last_period['status']}但奖金为{prize_amount}，数据不一致")
            self.stats['data_inconsistent'] += 1

        # 4. 根据 stop_on_win 判断是否停止追号
        if stop_on_win and prize_amount > 0:
            # 中奖即停模式：中奖了，停止追号
            self.mark_as_stopped(chase_id, f'中奖{prize_amount}元')
            return

        # 5. 检查是否还有剩余期数
        if current_count >= total_count:
            # 已完成所有期数
            self.mark_as_completed(chase_id)
            return

        # 6. 插入下一期
        self.insert_next_period(chase_plan)
    
    def run(self):
        """主执行流程"""
        print("=" * 60)
        print(f"追号计划自动插入脚本启动")
        print(f"时间: {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}")
        print("=" * 60)
        
        if not self.connect_db():
            return
        
        try:
            # 获取需要处理的追号计划
            chase_plans = self.get_active_chase_plans()
            
            if not chase_plans:
                print("\n没有需要处理的追号计划")
                return
            
            print(f"\n找到 {len(chase_plans)} 个需要处理的追号计划")
            
            # 处理每个追号计划
            for chase_plan in chase_plans:
                self.stats['checked'] += 1
                self.process_chase_plan(chase_plan)
            
            # 输出统计信息
            print("\n" + "=" * 60)
            print("执行完成")
            print("=" * 60)
            print(f"检查数量: {self.stats['checked']}")
            print(f"插入期数: {self.stats['inserted']}")
            print(f"停止计划: {self.stats['stopped']} (中奖)")
            print(f"完成计划: {self.stats['completed']} (所有期数)")
            print(f"等待开奖: {self.stats['waiting']}")
            print(f"数据不一致: {self.stats['data_inconsistent']}")
            print(f"并发冲突: {self.stats['concurrent_conflicts']} (重复插入被阻止)")
            print(f"错误数量: {self.stats['errors']}")
            print("\n期号计算统计:")
            print(f"  从数据库获取: {self.stats['period_from_db']}")
            print(f"  智能计算: {self.stats['period_smart_calc']}")
            print(f"  验证失败: {self.stats['period_validation_failed']}")
            print(f"\n完成时间: {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}")

            # 并发安全提示
            if self.stats['concurrent_conflicts'] > 0:
                print(f"\n⚠️  注意：检测到 {self.stats['concurrent_conflicts']} 次并发冲突")
                print("   这表明可能有多个进程同时执行脚本")
                print("   唯一索引已成功阻止重复插入")
        except Exception as e:
            print(f"\n❌ 执行出错: {e}")
            import traceback
            traceback.print_exc()
        finally:
            self.close_db()


if __name__ == '__main__':
    inserter = ChaseNumberAutoInserter()
    inserter.run()

